﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Diagnostics;
using System.Text.RegularExpressions;
using Excel = Microsoft.Office.Interop.Excel;
using System.Collections;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection conn;
        public DataTable[] dt_rep = new DataTable[0];
        private delegate void Deleg();
        private Deleg del;
        private object month;
        
        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            try
            {
                conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");

                //DataTable dt = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_MonthSel", 30, null);
                //if (dt.Rows.Count > 0)
                //{
                //    foreach (DataRow dr in dt.Rows)
                //    {
                //        NewItem ni = new NewItem();
                //        ni.ParamID = dr[0];
                //        ni.ParamComment = dr[1].ToString();
                //        comboBox1.Items.Add(ni);
                //        if (Convert.ToDateTime(ni.ParamID).Month == Convert.ToDateTime(DateTime.Now.AddDays(-1)).Month
                //                && Convert.ToDateTime(ni.ParamID).Year == Convert.ToDateTime(DateTime.Now.AddDays(-1)).Year)
                //            comboBox1.SelectedIndex = comboBox1.Items.Count - 1;
                //    }
                //}
                dateTimePicker1.Value = DateTime.Now.Date.AddDays(-1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void StartCreateRep(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            //dt_rep.Clear();
            month = dateTimePicker1.Value.ToString("yyyyMMdd");
            del = new Deleg(CreateRep);
            this.Text += " Формируется";
            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null); //
            panel1.Enabled = false;
            dataGridView1.Enabled = false;
        }

        private void CallBackFunc(IAsyncResult aRes)
        {
            del.EndInvoke(aRes);
            panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_rep[0].DefaultView));
        }

        private void CreateRep()
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[1];
                par[0].name = "@lastdate";
                par[0].value = month;
                //dt_rep.Load(RepProc(conn));
                dt_rep = (new SqlStorProc()).SqlProcExecWithQuery(conn, "pas_OperInd", 300, par);
                conn.Close();

                //CreateExcel("", dt_rep);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                conn.Close();
            }

        }

        private string CreateExcel(string filename, DataTable[] dt_array)
        {

            if (dt_array.Length > 0)
            {
                string fname = "ОП.xlsx";
                System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                Hashtable myHashtable = new Hashtable();
                myHashtable = CheckExcellProcesses(myHashtable);

                Excel.Application exapp = new Excel.Application();
                Excel.Workbook exwb = exapp.Workbooks.Add(System.Reflection.Missing.Value);
                
                Excel.Worksheet exws = (Excel.Worksheet)exwb.Sheets[1];
                //int i = 0;
                //while(i < dt_array.Length - 1)
                //{
                //    exwb.Sheets.Add();
                //    i++;
                //}
                
                exapp.DisplayAlerts = false;
                try
                {
                    if (filename == "")
                    {
                        FileDialog sfd = new SaveFileDialog();
                        sfd.FileName = fname;
                        sfd.Filter = "Excel | *.xlsx";
                        if (sfd.ShowDialog() == DialogResult.OK) filename = sfd.FileName;
                        sfd = null;
                    }
                    if (Path.GetExtension(filename) == "xlsx") Path.ChangeExtension(filename, "xlsx");

                    if (filename != "")
                    {

                        foreach (DataColumn col in dt_array[0].Columns)
                        {
                            if (col.Ordinal > 0)
                            {
                                if (col.Caption.IndexOf('*') > -1 && col.Caption.IndexOf("*-") > -1)
                                {
                                    exws.get_Range(exws.Cells[1, col.Ordinal], exws.Cells[1, col.Ordinal]).Value2 = col.Caption.Substring(col.Caption.IndexOf('*') + 1, col.Caption.IndexOf("*-") - 1);
                                    exws.get_Range(exws.Cells[2, col.Ordinal], exws.Cells[2, col.Ordinal]).Value2 = col.Caption.Substring(col.Caption.IndexOf("*-") + 2);
                                }
                                else
                                    exws.get_Range(exws.Cells[1, col.Ordinal], exws.Cells[1, col.Ordinal]).Value2 = col.Caption;

                                exws.get_Range(exws.Cells[1, col.Ordinal], exws.Cells[1, col.Ordinal]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                                exws.get_Range(exws.Cells[1, col.Ordinal], exws.Cells[1, col.Ordinal]).Cells.Interior.Color = Excel.XlRgbColor.rgbSkyBlue;

                                exws.get_Range(exws.Cells[2, col.Ordinal], exws.Cells[2, col.Ordinal]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                                exws.get_Range(exws.Cells[2, col.Ordinal], exws.Cells[2, col.Ordinal]).Cells.Interior.Color = Excel.XlRgbColor.rgbSkyBlue;
                            }
                        }
                        string colname = "";
                        int i_flag = 0;
                        object[] cols = new object[0];
                        for (int i = 1; i <= dt_array[0].Columns.Count; i++)
                        {
                            string strcol = "";
                            if (exws.get_Range(exws.Cells[1, i], exws.Cells[1, i]).Value2 != null)
                            {
                                strcol = exws.get_Range(exws.Cells[1, i], exws.Cells[1, i]).Value2.ToString();
                            }

                            if (colname == strcol)
                            {
                                if (i_flag == 0) i_flag = i - 1;
                            }
                            else
                            {
                                if (i_flag > 0)
                                {
                                    exws.get_Range(exws.Cells[1, i_flag], exws.Cells[1, i - 1]).Merge(Type.Missing);                           
                                    exws.get_Range(exws.Cells[1, i - 1], exws.Cells[1, i - 1]).Value2 = colname;                                   
                                    Array.Resize(ref cols, cols.Length + 1);
                                    cols[cols.Length - 1] = new int[2] { i_flag, i - 1 };
                                    i_flag = 0;
                                }
                                colname = strcol;
                            }
                            if (i <= dt_array[0].Columns.Count)
                            {
                                if (exws.get_Range(exws.Cells[2, i], exws.Cells[2, i]).Value2 == null)
                                    exws.get_Range(exws.Cells[1, i], exws.Cells[2, i]).Merge(Type.Missing);
                            }
                        }

                        for (int i_sh = 1; i_sh < dt_array.Length; i_sh++)
                        {
                            ((Excel.Worksheet)exwb.Sheets[1]).Copy(Type.Missing,(Excel.Worksheet)exwb.Sheets[i_sh]);
                            exws = (Excel.Worksheet)exwb.Sheets[i_sh + 1];
                            exws.Name = dt_array[i_sh].Rows[0][0].ToString();
                            
                            int countcol = dt_array[i_sh].Columns.Count - 1;
                            int countrow = dt_array[i_sh].Rows.Count;

                            object[,] dt_buf = ToArray(dt_array[i_sh]);

                            exws.get_Range(exws.Cells[3, 1], exws.Cells[countrow + 2, countcol]).Value2 = dt_buf;
                            exws.get_Range(exws.Cells[3, 1], exws.Cells[countrow + 2, countcol]).Cells.Borders.Color = Excel.XlRgbColor.rgbBlack;

                            int[] vis = new int[11] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };

                            for (int i = 3; i < countrow + 3; i++)
                            {
                                if (exws.get_Range(exws.Cells[i, 1], exws.Cells[i, 1]).Value2 == null)
                                {
                                    exws.get_Range(exws.Cells[i, 1], exws.Cells[i, countcol]).Cells.Font.Bold = true;
                                    exws.get_Range(exws.Cells[i, 1], exws.Cells[i, countcol]).Cells.Interior.Color = Excel.XlRgbColor.rgbPaleTurquoise;
                                }
                                for (int j = 1; j < 12; j++)
                                    if (exws.get_Range(exws.Cells[i, j], exws.Cells[i, j]).Value2 != null)
                                        vis[j - 1]++;

                                if (Convert.ToDouble(exws.get_Range(exws.Cells[i, 38], exws.Cells[i, 38]).Value2) > Convert.ToDouble(exws.get_Range(exws.Cells[i, 39], exws.Cells[i, 39]).Value2))
                                    exws.get_Range(exws.Cells[i, 39], exws.Cells[i, 39]).Cells.Font.Color = Excel.XlRgbColor.rgbRed;
                                if (Convert.ToDouble(exws.get_Range(exws.Cells[i, 42], exws.Cells[i, 42]).Value2) > Convert.ToDouble(exws.get_Range(exws.Cells[i, 43], exws.Cells[i, 43]).Value2))
                                    exws.get_Range(exws.Cells[i, 43], exws.Cells[i, 43]).Cells.Font.Color = Excel.XlRgbColor.rgbRed;
                                if (Convert.ToDouble(exws.get_Range(exws.Cells[i, 59], exws.Cells[i, 59]).Value2) < 0)
                                    exws.get_Range(exws.Cells[i, 59], exws.Cells[i, 59]).Cells.Font.Color = Excel.XlRgbColor.rgbRed;
                                if (Convert.ToDouble(exws.get_Range(exws.Cells[i, 60], exws.Cells[i, 60]).Value2) < 0)
                                    exws.get_Range(exws.Cells[i, 60], exws.Cells[i, 60]).Cells.Font.Color = Excel.XlRgbColor.rgbRed;
                            }

                            for (int i = 1; i <= countcol; i++)
                            {
                                if (dt_array[0].Columns[i].Caption.IndexOf('%') > -1 || i == 63)
                                    exws.get_Range(exws.Cells[3, i], exws.Cells[countrow + 2, i]).NumberFormat = "##0.00%";
                                else
                                {
                                    if ((i >= 12 && i <= 27) || (i >= 31 && i <= 33) || i == 55 || i == 56 || i == 59 || i == 60)
                                        exws.get_Range(exws.Cells[3, i], exws.Cells[countrow + 2, i]).NumberFormat = "### ### ##0,";
                                    if(i == 67)
                                        exws.get_Range(exws.Cells[3, i], exws.Cells[countrow + 2, i]).NumberFormat = "### ### ### ##0";
                                    if (i == 49 || i == 46 || i == 43)
                                        exws.get_Range(exws.Cells[3, i], exws.Cells[countrow + 2, i]).NumberFormat = "### ### ##0.00";
                                    if (i == 35 || i == 64 || i == 65)
                                        exws.get_Range(exws.Cells[3, i], exws.Cells[countrow + 2, i]).NumberFormat = "### ### ##0";
                                }
                                exws.get_Range(exws.Cells[1, i], exws.Cells[1, i]).ColumnWidth = 10;

                                if (exws.get_Range(exws.Cells[3, i], exws.Cells[countrow + 2, i]).NumberFormat.ToString() == "##0.00%")
                                {
                                    object formul = 0;
                                    if (i == 28 || i == 29) formul = 1;

                                    Excel.FormatCondition cfOR =
                                        (Excel.FormatCondition)exws.get_Range(exws.Cells[3, i], exws.Cells[countrow + 2, i]).FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlLess, formul, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                                    
                                    cfOR.Font.Color = Excel.XlRgbColor.rgbRed;

                                    if(i != 63) exws.get_Range(exws.Cells[3, i], exws.Cells[countrow + 2, i]).Cells.Interior.Color = Excel.XlRgbColor.rgbMoccasin;
                                }
                               
                            }

                            //int[] vis = new int[11] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };

                            //for (int i = 3; i < countrow + 3; i++)
                            //{
                            //    if (exws.get_Range(exws.Cells[i, 1], exws.Cells[i, 1]).Value2 == null)
                            //    {
                            //        exws.get_Range(exws.Cells[i, 1], exws.Cells[i, countcol]).Cells.Font.Bold = true;
                            //        exws.get_Range(exws.Cells[i, 1], exws.Cells[i, countcol]).Cells.Interior.Color = Excel.XlRgbColor.rgbPaleTurquoise;
                            //    }
                            //    for (int j = 1; j < 12; j++)
                            //        if (exws.get_Range(exws.Cells[i, j], exws.Cells[i, j]).Value2 != null) 
                            //            vis[j - 1]++;

                            //    if (Convert.ToDouble(exws.get_Range(exws.Cells[i, 38], exws.Cells[i, 38]).Value2) > Convert.ToDouble(exws.get_Range(exws.Cells[i, 39], exws.Cells[i, 39]).Value2))
                            //        exws.get_Range(exws.Cells[i, 39], exws.Cells[i, 39]).Cells.Font.Color = Excel.XlRgbColor.rgbRed;
                            //    if (Convert.ToDouble(exws.get_Range(exws.Cells[i, 42], exws.Cells[i, 42]).Value2) > Convert.ToDouble(exws.get_Range(exws.Cells[i, 42], exws.Cells[i, 42]).Value2))
                            //        exws.get_Range(exws.Cells[i, 43], exws.Cells[i, 43]).Cells.Font.Color = Excel.XlRgbColor.rgbRed;
                            //}

                            
                            exws.get_Range(exws.Cells[2, 1], exws.Cells[2, countcol]).RowHeight = Convert.ToInt32(exws.get_Range(exws.Cells[2, 1], exws.Cells[2, dt_array[0].Columns.Count - 1]).RowHeight) * 3;
                            exws.get_Range(exws.Cells[1, 1], exws.Cells[2, countcol]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                            exws.get_Range(exws.Cells[1, 1], exws.Cells[2, countcol]).WrapText = true;
                            exws.get_Range(exws.Cells[1, 1], exws.Cells[2, countcol]).EntireColumn.AutoFit();


                            int fl = 0;
                            int colind = 0;
                            for (int i = 0; i < 11; i++)
                            {
                                if (vis[i] == 0) exws.get_Range(exws.Cells[1, i + 1], exws.Cells[countrow + 2, i + 1]).ColumnWidth = 0;
                                else
                                {
                                    fl++;
                                    if(fl < 2) colind = i;   
                                }
                            }

                            if (colind > 0)
                                for (int i = 1; i <= colind; i++)
                                    exws.Application.ActiveWindow.SplitColumn = i;
                            else
                            {
                                exws.Application.ActiveWindow.SplitColumn = 1;
                                exws.Application.ActiveWindow.SplitColumn = 2;
                            }
                            exws.Application.ActiveWindow.SplitRow = 1;
                            exws.Application.ActiveWindow.SplitRow = 2;
                            exws.Application.ActiveWindow.FreezePanes = true;

                            foreach (object o in cols)
                            {
                                int[] cilind = (int[])o;
                                exws.get_Range(exws.Cells[1, cilind[0]], exws.Cells[countrow + 2, cilind[1]]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;
                                exws.get_Range(exws.Cells[1, cilind[0]], exws.Cells[countrow + 2, cilind[1]]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;
                                exws.get_Range(exws.Cells[1, cilind[0]], exws.Cells[countrow + 2, cilind[1]]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;
                                exws.get_Range(exws.Cells[1, cilind[0]], exws.Cells[countrow + 2, cilind[1]]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;
                            }
                        }

                        ((Excel.Worksheet)exwb.Sheets[1]).Delete();
                        exwb.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        exwb.Close(Type.Missing, Type.Missing, Type.Missing);
                        exapp.Quit();
                        KillExcel(myHashtable);
                        MessageBox.Show("Файл сохранен");
                    }
                }
                catch (Exception ex)
                {
                    exapp.Quit();
                    KillExcel(myHashtable);
                    MessageBox.Show(ex.Message);
                }

                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
            }
            return filename;
        }

        private Excel.XlRgbColor SetColor(object value)
        {
            Excel.XlRgbColor color;

            if (value == DBNull.Value) color = Excel.XlRgbColor.rgbBlueViolet;
            else
            {
                double val = Convert.ToDouble(value);
                if (val < 0) color = Excel.XlRgbColor.rgbRed;
                else
                    if (val < 0.1) color = Excel.XlRgbColor.rgbWhite;
                    else color = Excel.XlRgbColor.rgbYellow;
            }

            return color;
        }

        private Hashtable CheckExcellProcesses(Hashtable myHashtable)
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            int iCount = 0;
            foreach (Process ExcelProcess in AllProcesses)
            {
                myHashtable.Add(ExcelProcess.Id, iCount);
                iCount = iCount + 1;
            }
            return myHashtable;
        }

        private void KillExcel(Hashtable myHashtable)
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            foreach (Process ExcelProcess in AllProcesses)
            {
                if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
                    ExcelProcess.Kill();
            }
            AllProcesses = null;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            CreateExcel("", dt_rep);
        }

        public static object[,] ToArray(DataTable data)
        {
            var ret = Array.CreateInstance(typeof(object), data.Rows.Count, data.Columns.Count - 1) as object[,];
            for (var i = 0; i < data.Rows.Count; i++)
                for (var j = 1; j < data.Columns.Count; j++)
                    ret[i, j - 1] = data.Rows[i][j];
            return ret;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "ОП все данные";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                if ((new ExpToExcel()).SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, false))
                    MessageBox.Show("Файл сохранен");
            }
        }
    }
}
